﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DX.CRC.Common.Entities;
using SMIS.Entities;
using System.Data;

namespace SMIS.Dals
{
    class Dal4Income : DalBase
    {
        //【高月 2013-3-31-164846】点击主菜单进行的全局查询
        public static List<Income> SearchAllIncomes()
        {
            QueryObject qo = CreateQO();
            qo.CmdText = @"SELECT [IID] as ID
                      ,[Staff]
                      ,[Month]
                      ,[Income] as Incomes
                      ,[CreateTime]
                      ,[IsActive]
                      ,[Remark]
                  FROM [IncomeInfo] where IsActive=1";

            return qo.xExecuteList<Income>();
        }

        //【高月 2013-3-31-164759】为了配合员工筛选条件进行查询
        public static List<Income> SearchSelectedStaffIncomes(string staff)
        {
            QueryObject qo = CreateQO();
            qo.CmdText = @"SELECT [IID] as ID
                      ,[Staff]
                      ,[Month]
                      ,[Income] as Incomes
                      ,[CreateTime]
                      ,[IsActive]
                      ,[Remark]
                  FROM [IncomeInfo] where IsActive=1 and Staff=@Staff";

            qo.AddParam("@Staff", staff);

            return qo.xExecuteList<Income>();
        }

        //【高月 2013-3-31-164645】在进行修改的时候需要避免月份发生重复
        public static bool UpdateSelectedIncome(Income income)
        {
            QueryObject qo = CreateQO();
            qo.CmdText = @"declare @Count int;
                           select @Count=COUNT(*) from [IncomeInfo] 
                           where @Month in (select [Month] from [IncomeInfo] where IID<>@IID and Staff=@Staff )
                           if @Count=0
                           begin
                           UPDATE [IncomeInfo]
                           SET [Month] = @Month
                            ,[Income] = @Income
                            ,[Remark] = @Remark
                           WHERE [IID] = @IID
                           end";

            qo.AddParam("@IID", income.ID);
            qo.AddParam("@Staff", income.Staff);
            qo.AddParam("@Month", income.Month);
            qo.AddParam("@Income", income.Incomes);
            qo.AddParam("@Remark", income.Remark);

            return qo.xExecuteNonQuery()==1;

        }

        public static List<Income> InsertIncome(Income income)
        {
            QueryObject qo = CreateQO();
            qo.CmdText = @"INSERT INTO [IncomeInfo]
                                   ([Month]
                                   ,[Staff]
                                   ,[Income] 
                                   ,[CreateTime]
                                   ,[IsActive]
                                   ,[Remark])
                             VALUES
                                   (@Month
                                   ,@Staff
                                   ,@Income
                                   ,GETUTCDATE()
                                   ,1
                                   ,@Remark)";

            qo.AddParam("@Month", income.Month);
            qo.AddParam("@Staff", income.Staff);
            qo.AddParam("@Income", income.Incomes);
            qo.AddParam("@Remark",income.Remark);

            return qo.xExecuteList<Income>();
        }

        public static List<Income> DeleteIncome(Income income)
        {
            QueryObject qo = CreateQO();

            qo.CmdText = @"update IncomeInfo
                        set IsActive=0
                        where IID=@IncomeID";

            qo.AddParam("@IncomeID", income.ID);

            return qo.xExecuteList<Income>();
        }
        
        //【高月 2013-3-31-164516】为了在收入浏览页面中实现员工条件筛选
        public static DataTable GetStaffs()
        {
            QueryObject qo = CreateQO();
            qo.CmdText = @"SELECT [SID]
                           ,[SName]
                           FROM [StaffInfo] where IsActive=1";

            return qo.xExecuteDataTable();
        }

        //【高月 2013-3-31-163619】为了获取要删除行的ID及对应的员工名字
        public static DataTable GetSelectedStaffID(int iID)
        {
            QueryObject qo = CreateQO();
            qo.CmdText = @"SELECT [IID]
                                 ,[Staff]
                  FROM [IncomeInfo] where IID=@IID";

            qo.AddParam("@IID", iID);

            return qo.xExecuteDataTable();
        }
    }
}
